﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Customer_Profile.eCardReport.DBDao;
using KVB.Common;
using System.Text;
using Customer_Profile.eCard.DBDao;
using DevExpress.Web.ASPxGridView;

namespace Customer_Profile.eCardReport
{
    public partial class DairyMaturityReport2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                DataSet dsProd = ProductDao.getAllProduct("ALL");
                dropProd.DataSource = dsProd;
                dropProd.DataTextField = "procduct_name";
                dropProd.DataValueField = "procduct_code";
                dropProd.DataBind();
                ListItem liAllprod = new ListItem();
                liAllprod.Text = "All";
                liAllprod.Value = "All";
                dropProd.Items.Insert(0, liAllprod);

                txtgedatebegin.Text = DateTime.Now.ToString("yyyy-MM-dd");
                DateTime dt = DateTime.Now.AddMonths(1);
                txtgedateend.Text = dt.ToString("yyyy-MM-dd");
            }
            BindDataView();
            int colCount = ASPxGridView1.Columns.Count;
            for (int i = 1; i < colCount; i++)
            {
                ((GridViewDataColumn)ASPxGridView1.Columns[i]).PropertiesEdit.DisplayFormatString = "c";
            }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {

        }

        private void BindDataView()
        {
            string startDate = txtgedatebegin.Text.Trim();
            string endDate = txtgedateend.Text.Trim();
            string strSQL1 = "";
            try
            {
                strSQL1 = "SELECT V1.ref_date 日期,V1.due_amount 到期金额,V2.due_amount_extend 续存金额,V3.settle_amount AS 申请结算金额 FROM ";
                strSQL1 += "( ";
                strSQL1 += "     SELECT r.ref_date,ISNULL(due_amount,0) AS due_amount FROM ";
                strSQL1 += "     ( ";
                strSQL1 += "     SELECT due_date,SUM(due_amount) AS due_amount ";
                strSQL1 += "     FROM dbo.mmdeals d  ";
                strSQL1 += "     WHERE CONVERT(DATE,due_date) BETWEEN '" + startDate + "' AND '" + endDate + "' AND status='PASS' AND if_returncard='N' ";
                strSQL1 += "     GROUP BY due_date ";
                strSQL1 += "     ) v RIGHT JOIN ref_date r ON v.due_date= r.ref_date  ";
                strSQL1 += "     WHERE CONVERT(DATE,r.ref_date) BETWEEN '" + startDate + "' AND '" + endDate + "' ";
                strSQL1 += " ) V1  ";
                strSQL1 += "  INNER JOIN  ";
                strSQL1 += " ( ";
                strSQL1 += "     SELECT r.ref_date,ISNULL(extend_amount,0) AS due_amount_extend FROM  ";
                strSQL1 += "     ( ";
                strSQL1 += "     SELECT due_date,SUM(ed.extend_amount) AS extend_amount ";
                strSQL1 += "     FROM dbo.mmdeals d  INNER JOIN dbo.extend_detail ed ON d.deal_no=ed.old_deal_no ";
                strSQL1 += "     WHERE CONVERT(DATE,due_date) BETWEEN '" + startDate + "' AND '" + endDate + "' AND if_extended='Y' AND status='PASS' AND if_returncard='N' ";
                strSQL1 += "     GROUP BY due_date ";
                strSQL1 += "     ) v RIGHT JOIN ref_date r ON v.due_date= r.ref_date  ";
                strSQL1 += "     WHERE CONVERT(DATE,r.ref_date) BETWEEN '" + startDate + "' AND '" + endDate + "' ";
                strSQL1 += " ) V2 ON v1.ref_date = v2.ref_date";
                strSQL1 += "  INNER JOIN  ";
                strSQL1 += " ( ";
                strSQL1 += "     SELECT r.ref_date,ISNULL(settle_amount,0) AS settle_amount FROM  ";
                strSQL1 += "     ( ";
                strSQL1 += "     SELECT due_date,SUM(ss.settle_amount) AS settle_amount ";
                strSQL1 += "     FROM dbo.mmdeals md INNER JOIN dbo.settle_subinfo ss ON md.deal_no=ss.deal_no ";
                strSQL1 += "     WHERE CONVERT(DATE,due_date) BETWEEN '" + startDate + "' AND '" + endDate + "' AND status='PASS' AND if_returncard='N' ";
                strSQL1 += "     GROUP BY due_date ";
                strSQL1 += "     ) v RIGHT JOIN ref_date r ON v.due_date= r.ref_date  ";
                strSQL1 += "     WHERE CONVERT(DATE,r.ref_date) BETWEEN '" + startDate + "' AND '" + endDate + "' ";
                strSQL1 += " ) V3 ON v1.ref_date = v3.ref_date";

                
                //KVB.Common.Log.WriteDefaultLog("每日到期SQL:", strSQL1);
                DataSet dsResult = ReportDao.DairySumReport(strSQL1);
                if (dsResult != null && dsResult.Tables.Count > 0)
                {
                    ASPxGridView1.Columns.Clear(); //清除列
                    ASPxGridView1.AutoGenerateColumns = true; //设置自动添加列
                    ASPxGridView1.DataSourceID = null;
                    ASPxGridView1.DataSource = dsResult.Tables[0];
                    ASPxGridView1.DataBind();
                    ASPxGridView1.AutoGenerateColumns = false;
                }
                //else
                //{
                //    ASPxGridView1.DataBind();
                //}
                ASPxGridView1.Columns[0].Width = 150;
            }
            catch (Exception ex)
            {
                Log.WriteDefaultLog("Maturity_BindDataView", ex.ToString());
            }
        }

        protected void btn_Export_Click(object sender, EventArgs e)
        {
            //DevExpress.XtraPrinting.CsvExportOptions options = new DevExpress.XtraPrinting.CsvExportOptions();
            //options.TextExportMode = DevExpress.XtraPrinting.TextExportMode.Text;
            gridExport.WriteXlsToResponse("日到期统计"); //, options);
        }
    }
}